﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using System.Data.SQLite;

namespace SqliteDriver
{
	public class SqlTableSchema
	{
		String tableName = "";
		List<DbView.FieldInfo> fields;

		public SqlTableSchema()
		{
			this.fields = new List<DbView.FieldInfo>();
		}

		public int Count { get { return this.fields.Count; } }

		public DbView.FieldInfo this[int i]
		{
			get { return this.fields[i]; }
		}

		public DbView.FieldInfo this[String name]
		{
			get
			{
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.Name.ToUpper().CompareTo(name.ToUpper()) == 0)
						return fi;
				return null;
			}
		}

		public DbView.FieldInfo IdentityField
		{
			get
			{
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.IsAuto)
						return fi;
				return null;
			}
		}

		public DbView.FieldInfo[] PKeyFields
		{
			get
			{
				int keyCount = 0;
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.IsPKey)
						++keyCount;
				DbView.FieldInfo[] pkeyList = new DbView.FieldInfo[keyCount];
				keyCount = 0;
				foreach (DbView.FieldInfo fi in this.fields)
					if (fi.IsPKey)
						pkeyList[keyCount++] = fi;
				return pkeyList;
			}
		}
		// transition away from this to raw field info
		public DbView.FieldInfo[] Fields
		{
			get { return this.fields.ToArray(); }
		}

		// very tied to the schema info
		public void Populate(DataTable schemaTable,
							String nameCol, String typeCol, String lenCol, String decimalCol, String pkeyCol, String autoCol,
							String fkeyCol, String indexCol, String calcCol, String defCol, String definitionCol, String nullCol)
		{
			this.tableName = schemaTable.TableName;

			// find 
			foreach (DataRow dr in schemaTable.Rows)
			{
				String name = this.DataFromCell(dr, nameCol);
				String sqlType = this.DataFromCell(dr, typeCol);
				String leng = this.DataFromCell(dr, lenCol);
				String dp = (String.IsNullOrEmpty(decimalCol)) ? "" : this.DataFromCell(dr, decimalCol);
				String pk = (String.IsNullOrEmpty(pkeyCol)) ? "" : this.DataFromCell(dr, pkeyCol);
				String fk = (String.IsNullOrEmpty(fkeyCol)) ? "" : this.DataFromCell(dr, fkeyCol);
				String auto = (String.IsNullOrEmpty(autoCol)) ? "" : this.DataFromCell(dr, autoCol);
				String index = (String.IsNullOrEmpty(indexCol)) ? "" : this.DataFromCell(dr, indexCol);
				String calc = (String.IsNullOrEmpty(calcCol)) ? "" :  this.DataFromCell(dr, calcCol);
				String nulls = (String.IsNullOrEmpty(nullCol)) ? "" : this.DataFromCell(dr, nullCol); nulls = (nulls.Length == 0) ? "Yes" : nulls; // Special. For null column blank means Yes
				String def = (String.IsNullOrEmpty(defCol)) ? "" :  this.DataFromCell(dr, defCol);
				String definition = (String.IsNullOrEmpty(definitionCol)) ? "" : this.DataFromCell(dr, definitionCol);
				//
				int len = (leng.ToUpper() == "MAX") ? -1 :
						  (leng.Length == 0) ? 0 : // 2000 driver
						   Convert.ToInt32(leng);
				DbView.FieldInfo fi = new DbView.FieldInfo(name, sqlType, len);
				fi.IsAuto = this.TfFromData(auto); // should resolve auto inc info to Y if present
				this.ParseAutoData(fi, auto);
				fi.IsPKey = this.TfFromData(pk);
				fi.IsFKey = this.TfFromData(fk);
				fi.IsIndexed = this.TfFromData(index);
				if (sqlType.ToUpper() == "NCHAR" || sqlType.ToUpper() == "NVARCHAR")
					fi.IsUnicode = true;
				fi.IsCalc = this.TfFromData(calc);
				fi.AllowsNulls = this.TfFromData(nulls);
				fi.HasDefault = this.TfFromData(def);
				fi.ConstraintDefinition = definition;
				fi.Dp = (dp.Length == 0) ? 0 : Convert.ToInt32(dp);
				this.fields.Add(fi);
			}
		}

		String DataFromCell(DataRow dr, String columnName)
		{
			if (!dr.Table.Columns.Contains(columnName)) return "";
			return dr[columnName].ToString();
		}

		bool TfFromData(String data)
		{
			if (data.Length == 0) return false;  // empty means no
			char c = data[0];
			if (c == 'Y' || c == 'y' || c == 'T' || c == 't') return true;
			if (c == '1') return true;
			if (c == '0') return true;
			// Unless N or No then true
			if ((c == 'N' || c == 'n') && data.Length <= 2) return false;
			// handle other cases as they arise
			return true;
		}
		// check for (m,m)
		void ParseAutoData(DbView.FieldInfo fi, String autoData)
		{
			DbView.SimpleRegex sr = new DbView.SimpleRegex(@"\(\s*(\d+)\s*,\s*(\d+)\s*\)");
			if (sr.IsMatch(autoData))
			{
				fi.AutoSeed = Convert.ToInt32(sr[1]);
				fi.AutoInc = Convert.ToInt32(sr[2]);
			}
		}


		public static DataTable MakeFieldInfoTable(string connStr, string tableName)
		{
			DataSet schemaDSet = new DataSet();
			DataTable infoTable;
			DbView.FieldInfo[] fields = GetFieldInfo(connStr, tableName);

			DataTable dt = Helper.MakeDataTable("Fields", "Field Name|Type|Length|Dp|Nulls?|PKey?|Auto?|Index?|FKey?", "SSIISSSSS");
			foreach (DbView.FieldInfo fi in fields)
			{
				dt.Rows.Add(MakeDataRow(fi));
			}
			// finally set the DataTable name to the name of the table whose schema this is.
			dt.TableName = tableName;
			return dt;
		}

		// This is now the engine method. A FieldInfo array is more manageable than a datatable, but the dt is for the report.
		public static DbView.FieldInfo[] GetFieldInfo(string connStr, string tableName)
		{
			DataSet schemaDSet = new DataSet();
			DataTable infoTable;
			SQLiteCommand mycommand = null;
			SQLiteDataReader reader;
			SQLiteConnection cnn = null;
			try
			{
				cnn = new SQLiteConnection(connStr);
				cnn.Open();
				mycommand = new SQLiteCommand(cnn);
				// 1
				mycommand.CommandText = String.Format("PRAGMA table_info({0})", tableName);
				infoTable = new DataTable();
				reader = mycommand.ExecuteReader();
				infoTable.Load(reader);
				schemaDSet.Tables.Add(infoTable);
				reader.Close();
				// 2
				mycommand.CommandText = String.Format("PRAGMA foreign_key_list({0})", tableName);
				infoTable = new DataTable();
				reader = mycommand.ExecuteReader();
				infoTable.Load(reader);
				schemaDSet.Tables.Add(infoTable);
				reader.Close();
				// 3
				mycommand.CommandText = String.Format("PRAGMA index_list({0});", tableName);
				infoTable = new DataTable();
				DataTable indexTable = new DataTable();
				reader = mycommand.ExecuteReader();
				indexTable.Load(reader);
				// use contents of that table to populate index fields.
				infoTable = Helper.MakeDataTable("index_fields", "name", "S");
				List<string> indexes = new List<string>();
				foreach (DataRow dr in indexTable.Rows)
					indexes.Add(dr[1].ToString());
				foreach (string index in indexes)
				{
					mycommand.CommandText = String.Format("PRAGMA index_info({0});", index);
					indexTable = new DataTable();
					reader = mycommand.ExecuteReader();
					indexTable.Load(reader);
					foreach (DataRow idr in indexTable.Rows)
					{
						infoTable.Rows.Add( new object[] { idr[2].ToString() });
					}
				}
				schemaDSet.Tables.Add(infoTable);
				reader.Close();
				// 4. schema
				mycommand.CommandText = String.Format("SELECT sql FROM sqlite_master WHERE type='table' and name = '{0}'", tableName);
				infoTable = new DataTable();
				reader = mycommand.ExecuteReader();
				infoTable.Load(reader);
				schemaDSet.Tables.Add(infoTable);
				reader.Close();

				cnn.Close();
			}
			catch (Exception e)
			{
				// may not work if connection fails
				cnn.Close();
				String message = String.Format("Sql Error: {0}\n" +
											   "connection: {1}\n" +
												"Sql: {2}\n",
												e.Message, connStr, mycommand.CommandText);
				Exception ex = new Exception(message);
				throw ex; // abort process having logged as much as possible.
				throw new Exception(e.Message);
			}
			/////////////////////////////////////////////////////////////////////////
			// 
			infoTable = schemaDSet.Tables[0]; // always one table or we're in trouble.
			DbView.FieldInfo [] fields = new DbView.FieldInfo[infoTable.Rows.Count];
			int idx = 0;
			foreach (DataRow dr in infoTable.Rows)
				fields[idx++] = SqliteSchemaParser.MakeFieldInfo(dr);
			// full 
			if (schemaDSet.Tables.Count > 1)
			{
				SqliteSchemaParser.AddFkInfo(schemaDSet.Tables[1], fields);
				if (schemaDSet.Tables.Count > 2)
				{
					SqliteSchemaParser.AddIndexInfo(schemaDSet.Tables[2], fields);
					if (schemaDSet.Tables.Count > 3)
						SqliteSchemaParser.AddMiscSchemaInfo(schemaDSet.Tables[3], fields);
				}
			}
			return fields;
		}

		public static Dictionary<string, DbView.FieldInfo[]> MakeDatabaseSchema(string connStr)
		{
			Dictionary<string, DbView.FieldInfo[]> schema = new Dictionary<string,DbView.FieldInfo[]>();
			DataTable dt = Helper.GenericQuery(connStr, SqliteTemplates.TABLENAMES_SQLTEMPLATE);
			foreach (DataRow dr in dt.Rows)
			{
				string tableName = dr[0].ToString();
				schema.Add(tableName, GetFieldInfo(connStr, tableName));
			}
			return schema;
		}


		//public static DataTable MakeFieldInfoTable(DataTable infoTable)
		//{
		//    // single cell contains the schema
		//    String schema = infoTable.Rows[0][0].ToString();
		//    DbView.FieldInfo [] fields = SqliteSchemaParser.Parse(schema);
		//    SqliteSchemaParser.AddIndexInfo(infoTable, fields);
		//    DataTable dt = Helper.MakeDataTable("Fields", "Field Name|Type|Length|Dp|Nulls?|PKey?|Auto?|Index?|FKey?", "SSIISSSSS");
		//    foreach (DbView.FieldInfo fi in fields)
		//    {
		//        dt.Rows.Add(MakeDataRow(fi));
		//    }
		//    return dt;
		//}

		private static object[] MakeDataRow(DbView.FieldInfo fieldInfo)
		{
			string yes = "Yes";
			object[] row = new object []{ fieldInfo.Name, fieldInfo.SqlType, fieldInfo.Length, fieldInfo.Dp, 
											(fieldInfo.AllowsNulls) ? yes : "No", 
											(fieldInfo.IsPKey) ? yes : "",
											(fieldInfo.IsAuto) ? yes : "",
											(fieldInfo.IsIndexed) ? yes : "",
											(fieldInfo.IsFKey) ? yes : ""};
			return row;
		}

	}

	public class SqlScriptingHelper
	{
		String sourceTable;
		readonly SqlTableSchema schema;

		public SqlTableSchema Schema { get { return this.schema; } }
		public SqlScriptingHelper(String sourceTable, SqlTableSchema schema)
		{
			this.sourceTable = sourceTable;
			this.schema = schema;
		}

		public void SetFieldFilter(String[] fieldList) { this.fieldFilter = fieldList; }

		public String ScriptKeyCompare() { return this.ScriptKeyCompare(this.sourceTable); }
		public String ScriptKeyCompare(String targetTable)
		{
			String script = "";
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (fi.IsPKey)
				{
					if (script.Length > 0)
						script += " and";
					script += String.Format(" {1}.{0}={2}.{0}", Helper.SafeSqlField(fi.Name), Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable));
				}
			}
			return script;
		}

		public String ScriptKeyList() { return this.ScriptKeyList(this.sourceTable); }
		public String ScriptKeyList(String targetTable)
		{
			String script = "";
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (fi.IsPKey)
				{
					if (script.Length > 0)
						script += ",";
					if (targetTable.Length > 0)
						script += String.Format(" {1}.{0}", Helper.SafeSqlField(fi.Name), Helper.SafeTableName(targetTable));
					else
						script += String.Format("{0}", Helper.SafeSqlField(fi.Name));
				}
			}
			return script;
		}

		public String ScriptUpdate() { return this.ScriptUpdate(this.sourceTable); }
		public String ScriptUpdate(String targetTable)
		{
			String script = "";
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (!fi.IsPKey && !fi.IsAuto && !fi.IsCalc && this.InFilter(fi))
				{
					if (script.Length > 0)
						script += ",\n";
					script += String.Format(" {1}.{0}={2}.{0}", Helper.SafeSqlField(fi.Name), Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable));
				}
			}
			return script;
		}

		private bool InFilter(DbView.FieldInfo fi)
		{
			// ok better with a Dictionary, but I don't think speed is of the essence here.
			if (this.fieldFilter == null) return true; // filter not active
			foreach (String s in this.fieldFilter)
				if (s.ToUpper() == fi.Name.ToUpper()) return true;
			return false;
		}

		public String ScriptAllFields()
		{
			String[] src = new String[this.schema.Count];
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (this.InFilter(fi)) // not expecting this method to be called with an active filter
					src[idx] = String.Format("{0}", Helper.SafeSqlField(fi.Name));
			}
			return String.Join(",", src);
		}

		public String ScriptAllButCalcFields()
		{
			List<String> src = new List<string>();
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (!fi.IsCalc && this.InFilter(fi))
					src.Add(String.Format("{0}", fi.SafeName));
			}
			return String.Join(",", src.ToArray());
		}

		public String ScriptAnyFieldNotEqual(String targetTable)
		{
			List<String> src = new List<string>();
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (!fi.IsCalc && this.InFilter(fi))
					src.Add(String.Format("{1}.{0} != {2}.{0}", fi.SafeName, Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable)));
			}
			return "(" + String.Join("\r\nor ", src.ToArray()) +")";
		}

		public bool TablesCompare(SqlTableSchema compareSchema)
		{
			for (int idx = 0; idx < this.schema.Count; ++idx)
			{
				DbView.FieldInfo fi = this.schema[idx];
				if (compareSchema[fi.Name] == null)
					return false;
			}
			return true;
		}

		public String ScriptTableRefresh(String targetTable)
		{
			String script = "/*  Script to refresh a table (0) */\n";
			/*
			 * delete from x where not exists (select * from t2 where pk = pk2)
			*/
			script += String.Format(@"
            delete from {0} where not exists
                   (select * from {1} where {2})
            ", Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable), this.ScriptKeyCompare());

			script += String.Format(@"
            update {0} set {2}
              from {1}
             where {3} 
               and {4}
            ", Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable), this.ScriptUpdate(),
					   this.ScriptKeyCompare(),
					   this.ScriptAnyFieldNotEqual(this.sourceTable));  // not really source table.


			script += String.Format(@"
            insert into {0} ({2})
            select {2} 
              from {1} 
             where not exists (select * from {0} where {3}) 
            ", Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable), this.ScriptAllButCalcFields(), this.ScriptKeyCompare());

			return script;
		}

		public String ScriptRemoveNotPresent(String targetTable)
		{
			/*
			 * delete from x where not exists (select * from t2 where pk = pk2)
			*/
			string script = String.Format(@"
            delete from {0} where not exists
                   (select * from {1} where {2})
            ", Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable), this.ScriptKeyCompare());
			return script;
		}

		public String ScriptUpdateDifferent(String targetTable)
		{
			string script = String.Format(@"
            update {0} set {2}
              from {1}
             where {3} 
               and {4}
            ", Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable), this.ScriptUpdate(),
					   this.ScriptKeyCompare(),
					   this.ScriptAnyFieldNotEqual(this.sourceTable));  // not really source table.
			return script;
		}

		public String ScriptInsertMissing(String targetTable)
		{
			string script = String.Format(@"
            insert into {0} ({2})
            select {2} 
              from {1} 
             where not exists (select * from {0} where {3}) 
            ", Helper.SafeTableName(targetTable), Helper.SafeTableName(this.sourceTable), this.ScriptAllButCalcFields(), this.ScriptKeyCompare());

			return script;
		}


		// for import export set applicable filter for fields to script
		String[] fieldFilter = null;
	}
}
